package cn.learn.office.excel_v2;

import cn.learn.office.excel_v2.sort.HandlerColumnSort;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 多个sheet通用Excel工具类；更改：需要使用者传进来是使用哪一种规则
 *
 * @author huangyezhan
 * @version 2.0
 * @date 2020年4月20日12:01:09
 */
@Deprecated
public class ExportExcelUtil {
    private static final Logger logger = LoggerFactory.getLogger(ExportExcelUtil.class);
    private ExportExcelUtil() {
    }

    /**
     * 正则表达式，判断是否可以转为数字
     */
    private static final Pattern NUMBER_PATTERN = Pattern.compile("-?[0-9]+\\.?[0-9]*");

    /**
     * 简单的表格导出
     *
     * @param excelVo  Excel单个sheet数据
     * @param response
     */
    public static void simpleExportExcel(ExportExcelVo excelVo, HttpServletResponse response) {
        List<ExportExcelVo> list = new ArrayList<>();
        list.add(excelVo);
        simpleExportExcel(list, response);
    }

    /**
     * 简单的表格导出
     *
     * @param list     Excel多个sheet数据
     * @param response
     */
    public static void simpleExportExcel(List<ExportExcelVo> list, HttpServletResponse response) {
        String excelType = list.get(0).getExcelType();
        String excelName = list.get(0).getExcelName() + excelType;
        //最后生成转码码后的excel文件名称
        String       fileName = null;
        OutputStream out      = null;
        Workbook     wb       = null;
        try {
            //excel表格内容填充
            wb = creatExcel(list);
            fileName = new String(excelName.getBytes("utf-8"), "iso8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            out = response.getOutputStream();
            wb.write(out);
        } catch (IOException | IllegalAccessException e) {
            logger.info(e.toString());
        } finally {
            close(null, out, wb);
        }
    }

    /**
     * 生成Excel表格,填充表格数据
     *
     * @param excelVos
     * @return
     * @throws IllegalAccessException
     */
    public static Workbook creatExcel(List<ExportExcelVo> excelVos) throws IllegalAccessException {
        Workbook wb = null;
        //生成不同类型的excel表格
        if (".xls".equalsIgnoreCase(excelVos.get(0).getExcelType())) {
            wb = new HSSFWorkbook();
        } else {
            wb = new XSSFWorkbook();
        }

        //表格样式对象，字体对象
        Map<ExportExcelVo.Position, CellStyle> createStyleInstance = createStyleInstances(wb);
        Sheet                                  sheet               = null;
        for (ExportExcelVo excelVo : excelVos) {
            sheet = excelVo.getSheetName() == null ? wb.createSheet() : wb.createSheet(excelVo.getSheetName());
            creatSheet(sheet, createStyleInstance, excelVo);
        }
        return wb;
    }

    /**
     * 生成需要的表格样式对象【目前只支持4种样式对象】
     *
     * @param wb
     * @return
     */
    private static Map<ExportExcelVo.Position, CellStyle> createStyleInstances(Workbook wb) {
        Map<ExportExcelVo.Position, CellStyle> map = new HashMap<>(64);
        //表格内容样式
        CellStyle styleCenter = creatStyle(wb, ExportExcelVo.Position.CENTER);
        CellStyle styleLeft   = creatStyle(wb, ExportExcelVo.Position.LEFT);
        CellStyle styleRight  = creatStyle(wb, ExportExcelVo.Position.RIGHT);
        //表头样式
        CellStyle header      = creatStyle(wb, ExportExcelVo.Position.CENTER);
        CellStyle styleHeader = setHeaderStyle(wb, header);
        //存储对象
        map.put(ExportExcelVo.Position.CENTER, styleCenter);
        map.put(ExportExcelVo.Position.LEFT, styleLeft);
        map.put(ExportExcelVo.Position.RIGHT, styleRight);
        map.put(ExportExcelVo.Position.HEADER_CENTER, styleHeader);
        return map;
    }

    /**
     * Excel表格的统一的常用样式，居中、居左、居右、换行、边框
     *
     * @param wb        wb对象
     * @param alignment 水平对齐方式
     * @return 返回样式对象
     */
    public static CellStyle creatStyle(Workbook wb, ExportExcelVo.Position alignment) {
        CellStyle style = wb.createCellStyle();
        int       value = alignment.getValue();
        switch (value) {
            case 1:
                style.setAlignment(HorizontalAlignment.LEFT);
                break;
            case 2:
                style.setAlignment(HorizontalAlignment.RIGHT);
                break;
            default:
                style.setAlignment(HorizontalAlignment.CENTER);
        }
        //垂直方向居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置边框,合并单元格的时候有问题(不能用这个实例)
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        //设置自动换行
        style.setWrapText(true);
        return style;
    }

    /**
     * 表头首行样式
     *
     * @param wb wb对象
     * @return 返回样式对象
     */
    public static CellStyle setHeaderStyle(Workbook wb, CellStyle style) {
        Font font = wb.createFont();
        font.setFontName("宋体");
        /**
         * 粗体,字体的大小,水平居中,垂直居中
         */
        font.setBold(true);
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);
        //设置边框,合并单元格的时候有问题(不能用这个实例)
        style.setBorderTop(BorderStyle.MEDIUM);
        style.setBorderLeft(BorderStyle.MEDIUM);
        style.setBorderRight(BorderStyle.MEDIUM);
        style.setBorderBottom(BorderStyle.MEDIUM);
        //颜色填充
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //前景颜色
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //背景颜色
        style.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        return style;
    }

    /**
     * 生成sheet页
     *
     * @param sheet               sheet的对象
     * @param createStyleInstance 样式对象
     * @param excelVo             excel表格需要数据
     */
    private static void creatSheet(Sheet sheet, Map<ExportExcelVo.Position, CellStyle> createStyleInstance, ExportExcelVo excelVo) {
        List list = excelVo.getList();
        if (list != null && list.size() > 0) {
            //获取不同排序策略的对象，调用对应的列名去排序
            ExcelSortFactory  instance = ExcelSortFactory.getInstance();
            HandlerColumnSort install  = instance.createExcelSort(excelVo.getHandlerSort());
            install.columnSort(excelVo, sheet, createStyleInstance);
        }
        //最后生成表头，为了列自适应所以表头放最后生成
        creatHeader(sheet, createStyleInstance, excelVo);
    }

    /**
     * 创建表头
     *
     * @param excelVo 数据
     * @param sheet   sheet页对象
     */
    private static void creatHeader(Sheet sheet, Map<ExportExcelVo.Position, CellStyle> createStyleInstance, ExportExcelVo excelVo) {
        CellStyle style = null;
        style = createStyleInstance.get(ExportExcelVo.Position.HEADER_CENTER);

        String   multipleHeader = excelVo.getMultipleHeader();
        String[] singleHeader   = excelVo.getSingleHeader();
        if (multipleHeader != null) {
            //多表头
            setHeaderDatas(sheet, style, excelVo.getColumnWidth(), multipleHeader);
        } else {
            //单表头
            setHeaderDatas(sheet, style, excelVo.getColumnWidth(), singleHeader);
        }
    }


    /**
     * 设置表头的数据，单表头
     *
     * @param sheet
     * @param style
     * @param columnWidth 自定义的每列宽度
     * @param titles      表头
     */
    private static void setHeaderDatas(Sheet sheet, CellStyle style, int[] columnWidth, String[] titles) {
        Row  row  = null;
        Cell cell = null;
        //单行表头,固定第一行表头
        sheet.createFreezePane(0, 1, 0, 1);
        row = sheet.createRow(0);
        row.setHeight((short) (20 * 20));
        //迭代生成首行标题,并设置表头样式
        if (titles != null && titles.length > 0) {
            for (int i = 0; i < titles.length; i++) {
                cell = row.createCell(i);
                cell.setCellValue(titles[i]);
                cell.setCellStyle(style);
                if (columnWidth == null) {
                    sheet.autoSizeColumn(i, true);
                } else {
                    if (i < columnWidth.length) {
                        sheet.setColumnWidth(i, columnWidth[i]);
                    }
                }
            }
        }
    }

    /**
     * 设置表头的数据，多表头
     *
     * @param sheet         sheet对象
     * @param style         样式对象
     * @param columnWidth   自定义的每列宽度
     * @param title         表头
     */
    private static void setHeaderDatas(Sheet sheet, CellStyle style, int[] columnWidth, String title) {
        Row  row  = null;
        Cell cell = null;
        //合并 行、列
        CellRangeAddress region = null;

        JSONArray titles = JSONArray.parseArray(title);
        //行row
        for (int i = 0; i < titles.size(); i++) {
            row = sheet.createRow(i);
            JSONArray t = (JSONArray) titles.get(i);
            //列column
            for (int j = 0; j < t.size(); j++) {

                JSONObject o = (JSONObject) t.get(j);
                //设置合并行、列
                String name    = (String) o.get("tileName");
                int    rowspan = (int) o.get("rowspan");
                int    colspan = (int) o.get("colspan");

                //合并列
                if (rowspan > 1 || colspan > 1) {
                    if (rowspan < 1) {
                        rowspan = i;
                    }
                    if (colspan < 0) {
                        colspan = j;
                    }
                    region = new CellRangeAddress(i,  i + rowspan - 1, j, j + colspan - 1);
                    sheet.addMergedRegion(region);
                }
                cell = row.createCell(j);
                if (rowspan > 0 && colspan > 0) {
                    cell.setCellValue(name);
                }
                cell.setCellStyle(style);

                if (columnWidth == null) {
                    sheet.autoSizeColumn(i, true);
                } else {
                    if (columnWidth != null && i < columnWidth.length) {
                        sheet.setColumnWidth(i, columnWidth[i]);
                    }
                }
            }
        }
    }

    /**
     * 【注意：】
     * 如果显示到Excel表格的数据对数据类型没要求,可以全部转String,
     * 这里只有部分数据类型,需要其他的,自己手动添加
     *
     * @param createStyleInstance cell可能会用到样式对象
     * @param o1                  cell的数据值
     * @param cell                cell对象
     */
    public static void fillDatabyRealType(Map<ExportExcelVo.Position, CellStyle> createStyleInstance, Object o1, Cell cell) {
        //获取真实的数据类型，如果为空值null，随便给一个指定字符串
        String type = null;
        if (o1 != null) {
            type = o1.getClass().getName();
            type = type.substring(type.lastIndexOf(".") + 1, type.length());
        } else {
            type = "LEFT";
        }

        CellStyle style = null;
        switch (type) {
            case "Long":
                style = createStyleInstance.get(ExportExcelVo.Position.RIGHT);
                cell.setCellValue((Long) o1);
                break;
            case "Double":
                style = createStyleInstance.get(ExportExcelVo.Position.RIGHT);
                cell.setCellValue((Double) o1);
                break;
            case "BigDecimal":
                style = createStyleInstance.get(ExportExcelVo.Position.RIGHT);
                BigDecimal b = (BigDecimal) o1;
                cell.setCellValue(b.doubleValue());
                break;
            case "String":
                style = createStyleInstance.get(ExportExcelVo.Position.LEFT);
                cell.setCellValue((String) o1);
                break;
            case "Timestamp":
                style = createStyleInstance.get(ExportExcelVo.Position.CENTER);
                cell.setCellValue(String.valueOf(o1));
                break;
            default:
                style = createStyleInstance.get(ExportExcelVo.Position.LEFT);
                if (o1 == null) {
                    cell.setCellValue("");
                } else {
                    cell.setCellValue(String.valueOf(o1));
                }
        }
        cell.setCellStyle(style);
    }

    /**
     * 判断该字符串能不能转成 数字类型
     *
     * @param value
     * @return
     */
    private static boolean isStr2Num(String value) {
        Matcher matcher = NUMBER_PATTERN.matcher(value);
        return matcher.matches();
    }

    /**
     * 关闭OutputStream和关闭Workbook
     *
     * @param output 关闭流
     * @param wb     关闭Workbook对象
     */
    public static void close(InputStream input, OutputStream output, Workbook wb) {
        try {
            if (output != null) {
                output.flush();
                output.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (wb != null) {
                    wb.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (input != null) {
                        input.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

}
